#pragma once
#include "ExcelFile.h"
#include "SqlMaster.h"

namespace ExcelFile_Test {

	using namespace System;
	using namespace System::ComponentModel;
	using namespace System::Collections;
	using namespace System::Windows::Forms;
	using namespace System::Data;
	using namespace System::Drawing;

	/// <summary>
	/// Summary for Form1
	///
	/// WARNING: If you change the name of this class, you will need to change the
	///          'Resource File Name' property for the managed resource compiler tool
	///          associated with all .resx files this class depends on.  Otherwise,
	///          the designers will not be able to interact properly with localized
	///          resources associated with this form.
	/// </summary>
	public ref class Form1 : public System::Windows::Forms::Form
	{
	public:
		Form1(void)
		{
			InitializeComponent();
			G_exfile = gcnew ExcelFile;
			G_Master = gcnew SqlMaster;
			//
			//TODO: Add the constructor code here
			//
		}

	protected:
		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		~Form1()
		{
			if (components)
			{
				delete components;
			}
		}
	private: System::Windows::Forms::Button^  button1;
			 ExcelFile^ G_exfile;
			 SqlMaster^ G_Master;
			 
	protected: 

	private:
		/// <summary>
		/// Required designer variable.
		/// </summary>
		System::ComponentModel::Container ^components;

#pragma region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		void InitializeComponent(void)
		{
			this->button1 = (gcnew System::Windows::Forms::Button());
			this->SuspendLayout();
			// 
			// button1
			// 
			this->button1->Location = System::Drawing::Point(69, 105);
			this->button1->Name = L"button1";
			this->button1->Size = System::Drawing::Size(152, 54);
			this->button1->TabIndex = 0;
			this->button1->Text = L"button1";
			this->button1->UseVisualStyleBackColor = true;
			this->button1->Click += gcnew System::EventHandler(this, &Form1::button1_Click);
			// 
			// Form1
			// 
			this->AutoScaleDimensions = System::Drawing::SizeF(6, 13);
			this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
			this->ClientSize = System::Drawing::Size(284, 262);
			this->Controls->Add(this->button1);
			this->Name = L"Form1";
			this->Text = L"Form1";
			this->ResumeLayout(false);

		}
#pragma endregion
	private: System::Void button1_Click(System::Object^  sender, System::EventArgs^  e) {
				/*
				G_exfile->Create();
				array<String^>^ namesTables = {"bookmarks","bookmarks_list","data","dateaction","dictionary","history","settings"};
				for (int i = 0; i < namesTables->Length; i++){
					array<String^>^ fieldsName = G_Master->ReadFields("Test.his", namesTables[i]);//{"DT","RotatingSpeedPED"};
					array<array<String^>^>^ selection = G_Master->ReadTable("Test.his", namesTables[i], fieldsName);
					G_exfile->InsertSheet(namesTables[i],fieldsName,selection);
				}
				G_exfile->SaveClose("newBook.xls");
				*/
				
				
				SQLiteConnection^ connection = gcnew SQLiteConnection(String::Format("Data Source={0}; Version=3;", "Test.his"));
				SQLiteDataReader^ reader; 
				connection->Open();
				
				 
				 
				 
							 
				G_exfile->Create();
				G_exfile->VisibleApp(true);
				array<String^>^ namesTables = {"bookmarks","bookmarks_list","data","dateaction","dictionary","history","settings"};
				for (int i = 0; i < namesTables->Length; i++){
					array<String^>^ fieldsName = G_Master->ReadFields("Test.his", namesTables[i]);//{"DT","RotatingSpeedPED"};
					String^ sql = "SELECT ";
					for (int j= 0; j < fieldsName->Length-1; j++ ){
						sql += fieldsName[j]->ToString()+",";
					}
					sql += fieldsName[fieldsName->Length-1]->ToString()+" FROM " + namesTables[i] + ";";
					
					SQLiteCommand^ command = gcnew SQLiteCommand(sql, connection);
					reader = command->ExecuteReader();
					
					G_exfile->InsertSheet(namesTables[i],fieldsName,reader);
			    }
				G_exfile->SaveClose("newBook.xls");
				
			}	  
	};
}

